package cn.edu.ncu.housinghub.mapper.provider;

import cn.edu.ncu.housinghub.dto.request.house.HouseConditionRequest;
import cn.edu.ncu.housinghub.dto.request.house.NearbyHouseRequest;
import org.springframework.stereotype.Component;

/**
 * 这是mapper下的provider里面的（带条件查询房源信息）的 sql
 */
@Component
public class HouseSqlProvider {
    public String provideSelectHousesByCondition(HouseConditionRequest condition) {
//        System.out.println("DEBUG: 查询条件 - " + condition);

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("houseId, ");
        sql.append("landLordId, ");
        sql.append("houseSummary, ");
        sql.append("agentId, ");
        sql.append("complementaryFacilityId, ");
        sql.append("propertyVerifyId, ");
        sql.append("houseLocation, ");
        sql.append("layout, ");
        sql.append("FLOOR, ");
        sql.append("orientation, ");
        sql.append("STATUS, ");
        sql.append("rent, ");
        sql.append("paymentType, ");
        sql.append("houseRentType, ");
        sql.append("subwayInfo, ");
        sql.append("createTime, ");
        sql.append("isDeleted ");
        sql.append("FROM house WHERE 1=1 ");

        if (condition.getProvince() != null && !condition.getProvince().isEmpty()) {
            sql.append("AND houseLocation LIKE CONCAT(#{province}, '%') ");
        }

        if (condition.getCity() != null && !condition.getCity().isEmpty()) {
            sql.append("AND houseLocation LIKE CONCAT(#{province}, #{city}, '%') ");
        }

        if (condition.getDistrict() != null && !condition.getDistrict().isEmpty()) {
            sql.append("AND houseLocation LIKE CONCAT(#{province}, #{city}, #{district}, '%') ");
        }

        if (condition.getLayout() != null) {
            sql.append("AND layout = #{layout} ");
        }

        if (condition.getRentMin() != null) {
            sql.append("AND rent >= #{rentMin} ");
        }

        if (condition.getRentMax() != null) {
            sql.append("AND rent <= #{rentMax} ");
        }

        if (condition.getOrientation() != null && !condition.getOrientation().isEmpty()) {
            sql.append("AND orientation = #{orientation} ");
        }

        if (condition.getHouseRentType() != null) {
            sql.append("AND houseRentType = #{houseRentType} ");
        }

        sql.append("AND isDeleted = false");

        //生成sql用于调试
//        String finalSql = sql.toString();
//        System.out.println("DEBUG: 生成的SQL - " + finalSql);

        return sql.toString();
    }

    public String provideSelectNearbyHouses(NearbyHouseRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("houseId, ");
        sql.append("landLordId, ");
        sql.append("houseSummary, ");
        sql.append("agentId, ");
        sql.append("complementaryFacilityId, ");
        sql.append("propertyVerifyId, ");
        sql.append("houseLocation, ");
        sql.append("layout, ");
        sql.append("FLOOR, ");
        sql.append("orientation, ");
        sql.append("STATUS, ");
        sql.append("rent, ");
        sql.append("paymentType, ");
        sql.append("houseRentType, ");
        sql.append("subwayInfo, ");
        sql.append("createTime, ");
        sql.append("isDeleted, ");
        sql.append("area,"); // 添加 area 字段
        sql.append("longitude, ");
        sql.append("latitude ");
        sql.append("FROM house WHERE 1=1 ");

        // 确保房源有经纬度信息
        sql.append("AND longitude IS NOT NULL AND latitude IS NOT NULL ");

        // 基于距离的查询
        sql.append("AND ST_Distance_Sphere(POINT(longitude, latitude), ")
                .append("POINT(#{longitude}, #{latitude})) <= #{radius} ");

        sql.append("AND isDeleted = false ");

        // 添加额外的筛选条件
        if (request.getLayout() != null) {
            sql.append("AND layout = #{layout} ");
        }

        if (request.getRentMin() != null) {
            sql.append("AND rent >= #{rentMin} ");
        }

        if (request.getRentMax() != null) {
            sql.append("AND rent <= #{rentMax} ");
        }

        if (request.getOrientation() != null && !request.getOrientation().isEmpty()) {
            sql.append("AND orientation = #{orientation} ");
        }

        if (request.getHouseRentType() != null) {
            sql.append("AND houseRentType = #{houseRentType} ");
        }

        // 添加 area 查询条件
        if (request.getAreaMin() != null) {
            sql.append("AND area >= #{areaMin} ");
        }

        if (request.getAreaMax() != null) {
            sql.append("AND area <= #{areaMax} ");
        }

        sql.append("ORDER BY ST_Distance_Sphere(POINT(longitude, latitude), ")
                .append("POINT(#{longitude}, #{latitude})) ASC ");

        // 添加分页限制（只添加一次）
        int offset = (request.getPageNum() - 1) * request.getPageSize();
        sql.append("LIMIT ").append(offset).append(", ").append(request.getPageSize());

        return sql.toString();
    }

    /**
     * 获取附近房源的数量,新增分页功能
     */
    public String provideCountNearbyHouses(NearbyHouseRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) ");
        sql.append("FROM house WHERE 1=1 ");

        // 确保房源有经纬度信息
        sql.append("AND longitude IS NOT NULL AND latitude IS NOT NULL ");

        // 基于距离的查询
        sql.append("AND ST_Distance_Sphere(POINT(longitude, latitude), ")
                .append("POINT(#{longitude}, #{latitude})) <= #{radius} ");

        sql.append("AND isDeleted = false ");

        // 添加额外的筛选条件
        if (request.getLayout() != null) {
            sql.append("AND layout = #{layout} ");
        }

        if (request.getRentMin() != null) {
            sql.append("AND rent >= #{rentMin} ");
        }

        if (request.getRentMax() != null) {
            sql.append("AND rent <= #{rentMax} ");
        }

        if (request.getOrientation() != null && !request.getOrientation().isEmpty()) {
            sql.append("AND orientation = #{orientation} ");
        }

        if (request.getHouseRentType() != null) {
            sql.append("AND houseRentType = #{houseRentType} ");
        }

        if (request.getAreaMin() != null) {
            sql.append("AND area >= #{areaMin} ");
        }

        if (request.getAreaMax() != null) {
            sql.append("AND area <= #{areaMax} ");
        }

        return sql.toString();
    }
}